package model.admin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import myutil.Connect;

import vo.BatchDetailsVo;
import vo.mixed.CourseListVo;
import vo.mixed.ProfessorListVo;


public class GetCourseListMo {
	
	public ArrayList<ProfessorListVo> getCourseProfessorDetails(int course_id, int department_id){
		ArrayList<ProfessorListVo> course_prof_list = new ArrayList<ProfessorListVo>();
		Connect c=new Connect();
		String query="";
		if(course_id!=0)
		query="select s.staff_name,s.staff_id" +
				     "  from course_details c," +
				     "       course_professor p, " +
				     "       college_staff s " +
				     " where c.course_id	=	p.course_id " +
				     "   and (s.staff_id 	=	p.professor_id_1 " +
				     "    or s.staff_id 	=	p.professor_id_2 " +
				     "    or s.staff_id 	=	p.professor_id_3 " +
				     "    or s.staff_id 	=	p.professor_id_4) " +
				     "   and p.course_id	=	"+course_id+" " +
				     "   and s.status		=	1;";
		if(department_id!=0)
			query="select s.staff_name,s.staff_id" +
		     "  from course_details c," +
		     "       course_professor p, " +
		     "       college_staff s " +
		     " where c.course_id	=	p.course_id " +
		     "   and (s.staff_id 	=	p.professor_id_1 " +
		     "    or s.staff_id 	=	p.professor_id_2 " +
		     "    or s.staff_id 	=	p.professor_id_3 " +
		     "    or s.staff_id 	=	p.professor_id_4) " +
		     "   and c.department_id=	"+department_id+"" +
		     "   and c.status		=	1;";
			try {
			
		ResultSet rs=c.getResult(query);

			while(rs.next()){
		ProfessorListVo single_object = new ProfessorListVo();
		single_object.setStaff_id(rs.getInt("s.staff_id"));
		single_object.setStaff_name(rs.getString("s.staff_name"));
		course_prof_list.add(single_object);
		System.out.println("Professor details"+single_object.getStaff_id()+" and name is "+single_object.getStaff_name());	
		
		}
	} catch (Exception e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
		
		return course_prof_list;
	}

public ArrayList<CourseListVo> getCourseList(int professor_id)
{
	ArrayList<CourseListVo> course_list= new ArrayList<CourseListVo>();
	String query="";
	if (professor_id==0)
		 query="select * from course_details where status=1;";
	else 
		query="select * " +
				"from " +
				"course_details" +
			  " where status	=	1 " +
			  "   and course_id in (select course_id " +
			  						 "from course_professor " +
			  						"where professor_id_1	=	"+professor_id+" " +
			  						   "or professor_id_2	=	"+professor_id+" " +
			  						   "or professor_id_3  =   "+professor_id+" " +
			  						   "or professor_id_4	=	"+professor_id+");";
	Connect c=new Connect();
	try {
	ResultSet rs=c.getResult(query);
System.out.println("\n "+query);
		while(rs.next()){
	
		CourseListVo single_object=new CourseListVo();
		single_object.setCourse_code(rs.getString("course_code"));
		single_object.setCourse_id(rs.getInt("course_id"));
		single_object.setCourse_name(rs.getString("course_name"));
		single_object.setCredit(rs.getInt("credit"));
		single_object.setDepartment_id(rs.getInt("department_id"));
		single_object.setLab(rs.getInt("lab"));
		single_object.setTheory(rs.getInt("theory"));

		course_list.add(single_object);
		System.out.println("siva "+single_object.getCourse_name());
		
	}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
	return course_list;
}
public  ArrayList<CourseListVo> getDegreeDepartmentCourseList(int degree_id, int department_id, int batch_id) throws Exception{
	ArrayList<CourseListVo> batch_department_list = new ArrayList<CourseListVo>(); 
	Connect c=new Connect();
	
	String query="";
	if (department_id==0)
		query="select c.course_name," +
    	" c.course_code," +
    	" c.course_id  " +
    	   " from degree_department d, " +
   		" course_details c, " +
   		" department_details dd " +
   "where d.department_id	=	dd.department_id " +
   	 "and c.department_id	=	d.department_id " +
   	 "and d.degree_id="+degree_id+"  " +
   	 "and c.status			=	1;";
	
	else if(department_id!=0 && degree_id!=0)
	query="select c.course_name," +
    	" c.course_code," +
    	" c.course_id  " +
   " from degree_department d, " +
   		" course_details c, " +
   		" department_details dd " +
   "where d.department_id	=	dd.department_id " +
   	 "and c.department_id	=	d.department_id " +
   	 "and d.department_id	= 	"+department_id+" " +
   	 "and d.degree_id		=	"+degree_id+"" +
   	 "and c.status			=	1;";
	else if(batch_id!=0 && degree_id!=0 && department_id==0)
	query="select c.course_name," +
	" c.course_code," +
	" c.course_id , " +
	" dd.department_name, " +
	" dd.department_id" +
" from degree_department d, " +
		" course_details c, " +
		" department_details dd " +
"where d.department_id	=	dd.department_id " +
	 "and c.department_id	=	d.department_id" +"  "+
	 "and d.status			=	1 " +
	 "and d.department_id	=	"+department_id+"  "+
	 "and d.batch_id		=	"+batch_id+" " +
	 "and d.degree_id		=	"+degree_id+";";
   	 System.out.println(query+"\n");
	ResultSet rs= null;
	try {
		rs = c.getResult(query);		
		while(rs.next()){
			CourseListVo single_object = new CourseListVo();
			single_object.setCourse_code(rs.getString("c.course_code"));
			single_object.setCourse_id(rs.getInt("c.course_id"));
//			single_object.setDepartment_id(rs.getInt("dd.department_id"));
//			single_object.setDepartment_name(rs.getString("dd.department_name"));
			single_object.setCourse_name(rs.getString("c.course_name"));
			
			batch_department_list.add(single_object);
			
			
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return batch_department_list;
}

}
